package com.example.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.example.pojo.assTestLogPojo;
import com.example.pojo.controlTestingPojo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDateTime;
import java.util.List;

/**
 * ClassName:controlPTestingMapp
 * Description:
 *
 * @Author 赵永峰
 * @Create 2024/5/30 22:53
 * @Version 1.0
 */
@Mapper
public interface controlTestingMapp {
//查询出控制计划需要的项目
@DS("baodingRussia")
    @Select("select * from controlTesting where controlTesting=#{controlTesting} and deleteSign=1 order by controlTesting asc,orderNumber asc")
    List<controlTestingPojo> allByOne(controlTestingPojo controlTesting);
    @DS("baodingRussia")
    @Select("select * from controlTesting where  deleteSign=1 order by controlTesting asc , orderNumber asc")
    List<controlTestingPojo> allByOneTesting(controlTestingPojo controlTesting);
//根据ID修改数据
    //   /**
    //     *查询某一个字段不重复项，并且返回查询回来的数值，作为字符串List传回
    //     */
@DS("baodingRussia")
    @Select("select distinct ${fieldName} from controlTesting")
    List<String> OneOneStr(String fieldName);
    /**
     *查询某一个字段不重复项，并且满足另一个字段某一个值，返回查询回来的数值，作为字符串List传回
     */
    @DS("baodingRussia")
    @Select("select distinct ${fieldNameA} from controlTesting where ${fieldNameB} = #{valueToMatch}" )
    List<String> findDistinctBValuesByA(String fieldNameA,String fieldNameB, String valueToMatch);
    /**
     *查询某一个字段不重复项，并且满足另外两个字段的值，返回查询回来的数值，作为字符串List传回
     */
    @DS("baodingRussia")
    @Select("select distinct ${fieldNameA} from controlTesting where ${fieldNameB} = #{valueToMatchB} and ${fieldNameC}=#{valueToMatchC} ORDER BY op asc" )
    List<String> findDistinctBValuesByAandB(String fieldNameA,String fieldNameB,String valueToMatchB,String fieldNameC,String valueToMatchC);
    //点击树形结构，把对应该序的数据均筛选出来
    @DS("baodingRussia")
    @Select("select * from controlTesting where workshopEnglish=#{workshopEnglish} and drawing=#{drawing} and op=#{op} and deleteSign=1 ORDER BY controlTesting asc, typeTesting asc")
    List<controlTestingPojo> controlTestingList(controlTestingPojo controlTestingPojo);
//新的数据库中用到的变量
@DS("baodingRussia")
@Select("select * from controlTesting_new where " +
        "${firstFieldName} like concat('%',#{firstFieldVal},'%') " +
        "and ${secondFieldName} like concat('%',#{secondFieldVal},'%') " +
        "and ${thirdFieldName} = #{thirdFieldVal} " +
        "and deleteSign=1")
    List<controlTestingPojo> strFieldThree(String firstFieldName, String firstFieldVal, String secondFieldName, String secondFieldVal, String thirdFieldName, String thirdFieldVal);
    //保存用户输入的检测数据
    @DS("baodingRussia")
    @Select("insert into controlTesting_DataAssembly_new ( workshop," +
            "drawing," +
            " op," +
            " project, " +
            "projectEnglish, " +
            "projectRussian," +
            " requirement, " +
            "requirementEnglish," +
            " requirementRussian," +
            " typeTesting," +
            " standard," +
            " ceiling," +
            " lower," +
            " frequency," +
            " department," +
            " projectType," +
            " orderNumber, " +
            "establishTime," +
            " UpdateTime," +
            " controlTesting," +
            " deleteSign," +
            " testingDate," +
            " valueUse," +
            " userId," +
            " workpiece," +
            "classes) " +
            "values (" +
            "#{workshop}," +
            "#{drawing}," +
            "#{op}," +
            "#{project}," +
            "#{projectEnglish}," +
            "#{projectRussian}," +
            "#{requirement}," +
            "#{requirementEnglish}," +
            "#{requirementRussian}," +
            "#{typeTesting}," +
            "#{standard}," +
            "#{ceiling}," +
            "#{lower}," +
            "#{frequency}," +
            "#{department}," +
            "#{projectType}," +
            "#{orderNumber}," +
            "#{establishTime}," +
            "#{UpdateTime}," +
            "#{controlTesting}," +
            "#{deleteSign}," +
            "#{testingDate}," +
            "#{valueUse}," +
            "#{userId}," +
            "#{workpiece}," +
            "#{classes})")
    void testingValInsert(controlTestingPojo controlTestingPojoList);
    //筛选出用户做趋势图的数据
    @DS("baodingRussia")
    @Select("SELECT * FROM (SELECT * FROM controlTesting_DataAssembly_new WHERE project = #{project} ORDER BY testingDate DESC LIMIT #{selectNum}) AS subquery ORDER BY testingDate ASC")
    List<controlTestingPojo> selectNum(controlTestingPojo controlTestingPojo);
    //在更正数据页面中，修改数据，接收的是修数字信息
    @DS("baodingRussia")
    @Select("update controlTesting_DataAssembly_new set ${field}=#{val},UpdateTime=#{dateTime} where id=#{idUse}")
    void testingEditNmb(Integer idUse, String field, double val, LocalDateTime dateTime);
    //在更正数据页面中，修改数据，接收的是修改文本信息
    @DS("baodingRussia")
    @Select("update controlTesting_DataAssembly_new set ${field}=#{val} where id=#{idUse}")
    void testingEditStr(Integer idUse, String field, String val, LocalDateTime dateTime);
    //给控制计划管理页面查询“频次检测”的信息，只筛选控制计划标记位的内容
    @DS("baodingRussia")
    @Select("select * from controlTesting_new where controlTesting=#{controlTesting} and deleteSign=1")
    List<controlTestingPojo> controlByclickId(controlTestingPojo controlTesting);
    @DS("baodingRussia")
    @Select("update controlTesting_new set " +
            "workshop=#{workshop}," +
            "drawing=#{drawing}," +
            "op=#{op}," +
            "project=#{project}," +
            "projectEnglish=#{projectEnglish}," +
            "projectRussian=#{projectRussian}," +
            "requirement=#{requirement}," +
            "requirementEnglish=#{requirementEnglish}," +
            "requirementRussian=#{requirementEnglish}," +
            "typeTesting=#{typeTesting}," +
            "standard=#{standard}," +
            "ceiling=#{ceiling}," +
            "lower=#{lower}," +
            "frequency=#{frequency}," +
            "department=#{department}," +
            "departmentEnglish=#{departmentEnglish}," +
            "departmentRussian=#{departmentRussian}," +
            "projectType=#{projectType}," +
            "UpdateTime=#{UpdateTime}," +
            "deleteSign=#{deleteSign}," +
            "orderNumber=#{orderNumber} " +
            "where id=#{id}")
    void putById(controlTestingPojo controlTestingPojo);
    //插入一行
    @DS("baodingRussia")
    @Select("insert into controlTesting_new (workshop, " +
            "drawing, " +
            "op, " +
            "project, " +
            "projectEnglish, " +
            "projectRussian, " +
            "requirement, " +
            "requirementEnglish, " +
            "requirementRussian, " +
            "typeTesting, " +
            "standard, " +
            "ceiling, " +
            "lower, " +
            "frequency, " +
            "department, " +
            "departmentEnglish, " +
            "departmentRussian, " +
            "projectType, " +
            "orderNumber, " +
            "establishTime, " +
            "UpdateTime, " +
            "controlTesting, " +
            "deleteSign," +
            "workBook)" +
            " values (" +
            "#{workshop}," +
            "#{drawing}," +
            "#{op}," +
            "#{project}," +
            "#{projectEnglish}," +
            "#{projectRussian}," +
            "#{requirement}," +
            "#{requirementEnglish}," +
            "#{requirementRussian}," +
            "#{typeTesting}," +
            "#{standard}," +
            "#{ceiling}," +
            "#{lower}," +
            "#{frequency}," +
            "#{department}," +
            "#{departmentEnglish}," +
            "#{departmentRussian}," +
            "#{projectType}," +
            "#{orderNumber}," +
            "#{establishTime}," +
            "#{UpdateTime}," +
            "#{controlTesting}," +
            "#{deleteSign}," +
            "#{workBook})")
    void InsertOne(controlTestingPojo controlTestingPojo);
    //根据ID，修改检测基准表中的某一个字段的值
    @DS("baodingRussia")
    @Update("update controlTesting_new set ${field}=#{val},UpdateTime=#{dateTime} where id=#{id}")
    void editOneFiledByControlTestingNew(Integer id, String field, String val,LocalDateTime dateTime);
    //根据传来的字段(机型号），查询共计有多少条
    @DS("baodingRussia")
    @Select("SELECT COUNT(*) FROM controlTesting_new WHERE drawing = #{filed} and deleteSign=1")
    int queryNumControlTexting(String filed);
    @DS("baodingRussia")
    @Select("SELECT COUNT(*) FROM controlTesting_DataAssembly_new WHERE ${fieldName} = #{fieldVal} and deleteSign=1")
    int queryNumTextingResult(String fieldName, String fieldVal);
    //依据两个字段，向后端查询装配检测日志的信息
    @DS("baodingRussia")
    @Select("select * from assTestLog where deleteSign=1 and ${oneFiledName}=#{oneFiledVal} and ${towFiledNane}=#{towFiledVal}")
    assTestLogPojo assTestLogInquiryByTowFiled(String oneFiledName, String oneFiledVal, String towFiledNane, String towFiledVal);
    //向装配检测日志的数据库中查询，根据一个字段（数据不唯一）
    @DS("baodingRussia")
    @Select("select * from assTestLog where deleteSign=1 and ${oneFiledName}=#{oneFiledVal}")
    List<assTestLogPojo> assTestLogInquiryByOneFiled(String oneFiledName, String oneFiledVal);
    //辅助函数：（检测标准）根据一个字段，查询出符合这个字段所有的数据
    @DS("baodingRussia")
    @Select("select * from controlTesting_new where deleteSign=1 and ${oneFiledName}=#{oneFiledVal}")
    List<controlTestingPojo> controlTestingInquiryOneFiled(String oneFiledName, String oneFiledVal);
    //辅助函数：（检测结果）根据三个字段，询符合等于这三个字段的数据
    @DS("baodingRussia")
    @Select("select * from controlTesting_DataAssembly_new " +
            "where deleteSign=1 " +
            "and ${oneFiledName}=#{oneFiledVal} " +
            "and ${towFiledNane}=#{towFiledVal} " +
            "and ${threeFiledName}=#{threeFiledVal}")
    List<controlTestingPojo> controlTestingDataInquiryThreeFiled(String oneFiledName, String oneFiledVal, String towFiledNane, String towFiledVal, String threeFiledName, String threeFiledVal);
    //辅助函数，(检测日志）向装配检测日志的数据库中一次插入多个数据
    @DS("baodingRussia")
    void assTestLogInsertAll(List<assTestLogPojo> assTestLogPojoList);
    //辅助函数：（检测日志）根据一个字段，按照另一个字段从大到小排序，查询出第一条（主要用在了查询最后计算日期的场景上）
    @DS("baodingRussia")
    @Select("select * from assTestLog where ${oneFiledName}=#{oneFieldVal} order by ${towFieldName} desc limit 1")
    assTestLogPojo assTestLogEndDateCount(String oneFiledName, String oneFieldVal, String towFieldName);
    //辅助函数：（检测数据）根据一个字段，查询这个字段下A值到B值的数据，主要用在查询两个日期之间的值，或者大于A，小于等于B的值；
    @DS("baodingRussia")
    @Select("select * from controlTesting_DataAssembly_new where ${towFieldName}=#{towFieldVal} and ${fieldName}>#{fieldValA} and ${fieldName}<#{fieldValB}")
    List<controlTestingPojo> queryOneFieldByAtoB(String fieldName, String fieldValA, String fieldValB,String towFieldName,String towFieldVal);
    //辅助函数：（检测数据）根据一个字段A等于某一值，B字段从大到小排序，筛选n行；
    @DS("baodingRussia")
    @Select("select * from controlTesting_DataAssembly_new where ${fieldNameA}=#{fieldValA} and testingDate<=#{startData} order by ${fieldNameB} desc limit #{n}")
    List<controlTestingPojo> testingQueryFieldAndBNum(String fieldNameA, String fieldValA, String fieldNameB, int n,String startData);
    //(检测日志)根据用户点击的刷新按钮，更新最新的计算日期：type是类型，例如：EB05PPK，或者其他的类型数据，doe:是检测类型中的第二位，例如，EB05PPK，M，就是代表要查询月度EB05PPK的值，也就是模糊查询EB05PPK-M，排序是dateUse列创建时间倒序排序；
    @DS("baodingRussia")
    @Select("SELECT * FROM assTestLog WHERE assTestLog LIKE CONCAT('%', #{filedVal}, '%') ORDER BY dateUse DESC LIMIT 1")
    assTestLogPojo assTestLogRefreshPPKDate(String filedVal);
    //辅助函数：（检测数据）根据一个字段，查询等于这个字段的所有数据
    @DS("baodingRussia")
    @Select("select * from controlTesting_DataAssembly_new where ${fieldName}=#{fieldVal}")
    List<controlTestingPojo> testingQueryOneFiled(String fieldName, String fieldVal);
}
